package io.renren.dao;

import io.renren.common.dao.BaseDao;
import io.renren.dto.StockOrderDTO;
import io.renren.dto.StockOrdermxDTO;
import io.renren.entity.StockOrdermxEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 食材采购明细表
 *
 * @author 翟文闯 619556390@QQ.com
 * @since 1.0.0 2019-11-22
 */
@Mapper
public interface StockOrdermxDao extends BaseDao<StockOrdermxEntity> {

    @Select("SELECT mx.*,f.food_name,f.unit,is_valid FROM canteens_stock_ordermx mx LEFT JOIN canteens_food f on mx.food_id=f.food_id where mx.stock_order_id='${orderId}'and mx.supplier_id='${supplierId}'")
    List<StockOrdermxDTO> detailed(long orderId,long supplierId);
    @Select("SELECT f.food_name,f.unit,A.create_date as create_date ,mx.* FROM canteens_stock_ordermx mx LEFT JOIN canteens_food f on mx.food_id=f.food_id  " +
            "LEFT JOIN canteens_stock_order A on a.stock_order_id = mx.stock_order_id" +
            " where mx.stock_order_id='${orderId}'and mx.supplier_id='${supplierId}'")
    List<StockOrdermxDTO> selecSupplierOrder1(long supplierId,long orderId);
    @Select("UPDATE canteens_stock_ordermx  set sub_status ='${subStatus}' where  stock_ordermx_id ='${orderId}'")
    void updateOrder(long orderId,int subStatus);
    /**
     * 财务查询食材订单详情
     *
     * @param orderId
     * @param supplierId
     * @return
     */
    @Select("SELECT mx.sub_status,mx.stock_ordermx_id FROM canteens_stock_ordermx mx LEFT JOIN canteens_food f on mx.food_id=f.food_id where mx.stock_order_id='${orderId}'and mx.supplier_id='${supplierId}'")
     List<StockOrdermxDTO> selectStockOrdermx(long orderId, long supplierId);
    /**
     * 查询订单下单供应商
     * @param id
     * @return
     */
    @Select("SELECT * from canteens_stock_ordermx mx,canteens_supplier s where mx.stock_order_id=" +"'${id}' and mx.supplier_id=s.supplier_id GROUP BY mx.supplier_id")
    List<StockOrdermxDTO> gys(long id);
    //查询已报价的供应商
    @Select("SELECT  m.create_date as mealTime,mx.*,s.* from canteens_stock_ordermx mx,canteens_supplier s,canteens_stock_order m where mx.stock_order_id=" +"'${id}'" +
            "and mx.stock_order_id = m.stock_order_id and mx.supplier_id=s.supplier_id and mx.sub_status = 1 GROUP BY mx.supplier_id")
    List<StockOrdermxDTO> gys1(long id);
    //根据id查询具体订单
    @Select("SELECT * from canteens_stock_ordermx mx where mx.stock_ordermx_id = " +"'${id}' ")
    StockOrdermxDTO gysMx(long id);

    @Select("DELETE from  canteens_stock_ordermx   where stock_order_id = " +
            "'${id}'")
    void deleteOrder(Long id);

    @Select("SELECT SUM(A.reality_num*A.unit_price)unit_price FROM canteens_stock_ordermx A ,canteens_stock_order B where B.`status`>=4 " +
            "and  DATE_FORMAT('${bgeinDate}','%Y-%m-%d') <= DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            " and DATE_FORMAT('${endDate}','%Y-%m-%d') >= DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            " and A.stock_order_id = B.stock_order_id " +
            " and A.supplier_id = '${supplierId}'")
    StockOrdermxDTO getOrderNum(String bgeinDate,String endDate,long supplierId);
    @Select("SELECT A.* FROM canteens_stock_ordermx A ,canteens_stock_order B where B.`status`>=4 " +
            "and  DATE_FORMAT('${bgeinDate}','%Y-%m-%d') <= DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            " and DATE_FORMAT('${endDate}','%Y-%m-%d') >= DATE_FORMAT(B.create_date,'%Y-%m-%d') " +
            " and A.stock_order_id = B.stock_order_id " +
            " and A.supplier_id = '${supplierId}'")
    List<StockOrdermxDTO> getOrderMx(String bgeinDate,String endDate,long supplierId);

    @Select("UPDATE canteens_stock_ordermx set purchase_num = '${purchaseNum}' where stock_order_id =${orderId}  and food_id = ${foodId} ")
    void updateOrder2(long orderId,long foodId,float purchaseNum);
    @Select("DELETE from canteens_stock_ordermx where stock_order_id =${orderId}  and food_id = ${foodId} ")
    void delectOrder1(long orderId,long foodId);
}